package com.ncmem.up7.database.sql;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import com.google.gson.Gson;
import com.ncmem.model.FileInf;
import com.ncmem.up7.database.DbHelper;
import com.ncmem.up7.database.oracle.OracleFile;
import com.ncmem.up7.sql.*;
import com.ncmem.utils.ConfigReader;
import com.ncmem.utils.DataBaseType;


/*
 * 原型
 */
public class SqlFile {

    public static SqlFile build(){

        if( ConfigReader.dbType() == DataBaseType.MySQL ) return new SqlFileMySQL();
        else if( ConfigReader.dbType() == DataBaseType.Oracle ||
                ConfigReader.dbType() == DataBaseType.DMDB) return new OracleFile();
        //else if( ConfigReader.dbType() == DataBaseType.KingBase ) return new OdbcFile();
        //else if( ConfigReader.dbType() == DataBaseType.MongoDB ) return new MongoFile();
        return new SqlFile();
    }

    public SqlFile()
    {
    }

    public SqlTable table(){
        return SqlTable.build("up7_files");
    }

    public static boolean existSameFile(String n,String pid,String uid)
    {
        SqlWhereMerge swm = new SqlWhereMerge();
        swm.equal("f_nameLoc", n);
        swm.equal("f_pid", pid);
        swm.equal("f_deleted", 0);
        swm.equal("f_uid", uid);

        SqlExec se = new SqlExec();
        return se.count("up7_files", swm.to_sql())>0;

    }

    /**
     * 清空文件表，文件夹表数据。
     */
    public void Clear()
    {
        DbHelper db = new DbHelper();
        db.ExecuteNonQuery("delete from up7_files");
        db.ExecuteNonQuery("delete from up7_folders");
    }

    public void merged(String id)
    {
        String sql = "update up7_files set f_lenSvr=f_lenLoc,f_perSvr='100%',f_merged=1 where f_id=?";
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);

        try
        {
            cmd.setString(1,id);
        } catch(SQLException e){
            e.printStackTrace();
        }
        db.ExecuteNonQuery(cmd);
    }

    public void add(FileInf f) throws ParseException, IllegalAccessException, SQLException {
        this.table().insert(f);
    }

    public void Add(FileInf f) throws ParseException, IllegalAccessException, SQLException {
        this.table().insert(f);
    }
    public void complete(String id,String uid) throws ParseException, IllegalAccessException, SQLException {
        this.table().update(
                SqlSeter.build()
                        .sql("f_lenSvr=f_lenLoc")
                        .set("f_perSvr", "100%")
                        .set("f_complete", true)
                        .set("f_scan", true),
                SqlWhere.build()
                        .eq("f_id", id)
                        .eq("f_uid",uid)
        );
    }

    public FileInf read(String id) throws ParseException, IllegalAccessException, SQLException {
        return this.table().readOne(FileInf.build(),
                SqlWhere.build().eq("f_id",id));
    }

    public FileInf read(String pathRel,String id,String uid) throws ParseException, IllegalAccessException, SQLException {
        return this.table().readOne(FileInf.build(),
                "f_id,f_pid,f_pidRoot,f_pathSvr,f_pathRel",
                SqlWhere.build()
                        .eq("f_pathRel", pathRel)
                        .eq("f_deleted", false)
                        .eq("f_uid", uid)
                        .ineq("f_id", id)
        );
    }

    public boolean read(FileInf m)
    {
        boolean ret = false;
        StringBuilder sb = new StringBuilder();
        sb.append("select");
        sb.append(" f_pathSvr");//1
        sb.append(",f_pathRel");//2
        sb.append(",f_md5");//3
        sb.append(",f_lenLoc");//4
        sb.append(",f_sizeLoc");//5
        sb.append(",f_pos");//6
        sb.append(",f_blockCount");//7
        sb.append(",f_blockSize");//8
        sb.append(",f_blockPath");//9
        sb.append(",f_lenSvr");//10
        sb.append(",f_pid");//11
        sb.append(",f_perSvr");//12
        sb.append(",f_complete");//13
        sb.append(",f_time");//14
        sb.append(",f_deleted");//15
        sb.append(",f_merged");//16
        sb.append(",f_pidRoot");//17
        sb.append(",f_fdTask");//18
        sb.append(",f_fdChild");//19
        sb.append(",f_uid");//20
        sb.append(",f_nameLoc");//21
        sb.append(",f_nameSvr");//22
        sb.append(",f_pathLoc");//23
        sb.append(" from up7_files where f_id=?");

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sb.toString());
        try {
            cmd.setString(1, m.id);
            ResultSet r = db.ExecuteDataSet(cmd);
            if(r.next())
            {
                m.pathSvr 		= r.getString(1);
                m.pathRel 		= r.getString(2);
                m.md5			= r.getString(3);
                m.lenLoc		= r.getLong(4);
                m.sizeLoc		= r.getString(5);
                m.blockCount 	= r.getInt(7);
                m.blockSize  	= r.getInt(8);
                m.blockPath  	= r.getString(9);
                m.lenSvr     	= r.getLong(10);
                m.pid        	= r.getString(11);
                m.perSvr     	= r.getString(12);
                m.complete   	= r.getBoolean(13);
                m.time = r.getDate(14);
                m.deleted    	= r.getBoolean(15);
                //m.merged   	= r.getBoolean(16);
                m.pidRoot    	= r.getString(17);
                m.fdTask     	= r.getBoolean(18);
                m.fdChild  	= r.getBoolean(19);
                m.uid        	= r.getString(20);
                m.nameLoc    	= r.getString(21);
                m.nameSvr    	= r.getString(22);
                m.pathLoc    	= r.getString(23);
                ret = true;
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {e.printStackTrace();}
        return ret;
    }

    public FileInf exist_file(String md5) throws ParseException, IllegalAccessException, SQLException {
        return this.table()
                .readOne(FileInf.build(),
                        SqlWhere.build()
                                .eq("f_md5",md5)
                                .eq("f_complete",true)
                );

    }
    public void remove(String id,String uid) throws ParseException, IllegalAccessException, SQLException {
        this.table().update(
                SqlSeter.build().set("f_deleted",true),
                SqlWhere.build()
                        .eq("f_id",id)
                        .eq("f_uid",uid)
        );
    }

    public String all_uncmp(String uid)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(" f_id");
        sb.append(",f_fdTask");
        sb.append(",f_nameLoc");
        sb.append(",f_pathLoc");
        sb.append(",f_pathSvr");
        sb.append(",f_blockPath");
        sb.append(",f_sizeLoc");
        sb.append(",f_perSvr");
        sb.append(" from up7_files");
        sb.append(" where f_uid=? and f_deleted=0 and f_complete=0");

        ArrayList<FileInf> files = new ArrayList<FileInf>();
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sb.toString());
        try {
            cmd.setString(1, uid);
            ResultSet r = db.ExecuteDataSet(cmd);
            while(r.next())
            {
                FileInf f 		= new FileInf();
                f.id 			= r.getString(1);
                f.fdTask 		= r.getBoolean(2);
                f.nameLoc 		= r.getString(3);
                f.pathLoc 		= r.getString(4);
                f.pathSvr 		= r.getString(5);
                f.blockPath 	= r.getString(6);
                f.sizeLoc 		= r.getString(7);
                f.perSvr 		= r.getString(8);
                files.add(f);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        if(files.size() < 1) return "";
        Gson g = new Gson();
        return g.toJson( files );
    }

    public void process(String id,String perSvr,long lenSvr) throws ParseException, IllegalAccessException, SQLException {
        this.table().update(SqlSeter.build()
                        .set("f_lenSvr",lenSvr)
                        .set("f_perSvr",perSvr),
                SqlWhere.build().eq("f_id",id)
        );
    }

    /// <summary>
    /// 删除一条数据，并不真正删除，只更新删除标识。
    /// </summary>
    /// <param name="f_uid"></param>
    /// <param name="f_id"></param>
    public void Delete(String f_uid,String f_id) throws ParseException, IllegalAccessException, SQLException {
        this.table().update(
                SqlSeter.build().set("f_deleted",true),
                SqlWhere.build()
                        .eq("f_id",f_id)
                        .eq("f_uid",f_uid)
        );
    }

    public void delete(String pathRel,String uid,String id) throws ParseException, IllegalAccessException, SQLException {
        this.table().update(
                SqlSeter.build().set("f_deleted",true),
                SqlWhere.build()
                        .eq("f_uid",uid)
                        .eq("f_pathRel",pathRel)
                        .eq("f_fdTask",false)
                        .ineq("f_id",id)
        );
    }

    public static boolean existSameFile(String n,String pid)
    {
        SqlWhereMerge swm = new SqlWhereMerge();
        swm.equal("f_nameLoc", n.trim());
        swm.equal("f_pid", pid.trim(), false);
        swm.equal("f_deleted", 0);

        SqlExec se = new SqlExec();
        return se.count("up7_files", swm.to_sql())>0;
    }

    /**
     * 更新子文件路径
     * @param pathRelOld
     * @param pathRelNew
     */
    public void updatePathRel(String pathRelOld,String pathRelNew)
    {
        //更新子文件路径
        String sql = String.format("update up7_files set f_pathRel=REPLACE(f_pathRel,'%s/','%s/') where CHARINDEX('%s/',f_pathRel)=1",
                pathRelOld,
                pathRelNew,
                pathRelOld
        );

        SqlExec se = new SqlExec();
        se.exec(sql);

    }
    //批量添加
    public void addBatch(List<FileInf> arr) throws SQLException, ParseException, IllegalAccessException {
        this.table().inserts(arr);
    }
}